

# 会务系统S11过度到S12，数据库调整脚本

import os
import mysql.connector

# mydb = mysql.connector.connect(
#   host="127.0.0.1",
#   user="root",
#   password="11111111",
#   database="venus_ultra"
# )

mydb = mysql.connector.connect(
  host="192.168.2.166",
  port="3378",
  user="venus_ultra",
  password="venus_ultra",
  database="venus_ultra"
)

mycursor = mydb.cursor()


# 创建数据库表
f = open("mysql/gonsin/create_tables.sql", "r", encoding="utf8")
for line in f:
    mycursor.execute(line)
f.close()
mydb.commit()


## 删除不用的字段
f = open("mysql/gonsin/drop_columns.sql", "r", encoding="utf8")
for line in f:
    mycursor.execute(line)
f.close()

## 修改字段类型
f = open("mysql/gonsin/modify_columns.sql", "r", encoding="utf8")
for line in f:
    mycursor.execute(line)
f.close()


## 根据user表的改动，调整  gs_user_user_and_edifice 表
## 查询gs_user人员列表
mycursor.execute("SELECT * FROM gs_user")
user_result = mycursor.fetchall()
users = []
for user in user_result:
    users.append(user)


for user in users:
    user_key = user[0]
    create_time = user[1]
    update_time = user[2]
    version = user[3]
    is_admin = user[4]
    child_num = user[5]
    default_audit = user[6]
    department_key = user[7]
    edifice_key = user[8]
    group_key = user[9]
    language = user[10]
    login_id = user[11]
    origin_user_key = user[12]
    parent_user_key = user[13]
    password = user[14]
    people = user[15]
    real_name = user[16]
    is_root = user[17]
    root_user_key = user[18]
    salt = user[19]
    skin = user[20]
    soft = user[21]
    state = user[22]
    user_message_key = user[23]
    user_type = user[24]
    username = user[25]
    out_id = user[26]


    ### 查找edifice 和 user关系表
    def findEdificeRelationByKey(user_key, edifice_key):
        mycursor.execute(f"SELECT * FROM gs_user_user_and_edifice where user_key = '{user_key}' and edifice_key = '{edifice_key}' ")
        edifice_relation = mycursor.fetchone()
        return edifice_relation


    ## 插入edifice 关系表
    def insertEdificeRelation(edifice_relation):
        mycursor = mydb.cursor()
        sql = "INSERT INTO gs_user_user_and_edifice (user_key, edifice_key) VALUES (%s, %s)"
        mycursor.execute(sql, edifice_relation)
        mydb.commit()


    ### 查找 group 和 user关系表
    def findGroupRelationByKey(user_key, group_key):
        mycursor.execute(f"SELECT * FROM gs_user_user_and_group where user_key = '{user_key}' and group_key = '{group_key}' ")
        group_relation = mycursor.fetchone()
        return group_relation
    

    ## 插入group 关系表
    def insertGroupRelation(group_relation):
        mycursor = mydb.cursor()
        sql = "INSERT INTO gs_user_user_and_group (user_key, group_key, edifice_key) VALUES (%s, %s, %s)"
        mycursor.execute(sql, group_relation)
        mydb.commit()


    ## 查找人员资料
    def findUserMessageByKey(user_message_key):
        mycursor.execute(f"SELECT * FROM gs_user_message where user_message_key = '{user_message_key}'")
        user_mnessage = mycursor.fetchone()
        return user_mnessage
    

    ## 查找用户组
    def findGroupByKey(group_key):
        mycursor.execute(f"SELECT * FROM gs_user_group where group_key = '{group_key}'")
        group = mycursor.fetchone()
        return group
    

    def updateUserMessageByKey(user_message_key, login_id = None, people = None, user_key = None):
        if(login_id):
            mycursor.execute(f"UPDATE gs_user_message SET login_id = '{login_id}' WHERE user_message_key = '{user_message_key}'")
        if(people is not None):
            mycursor.execute(f"UPDATE gs_user_message SET is_people = '{people}' WHERE user_message_key = '{user_message_key}'")
        if(user_key):
            mycursor.execute(f"UPDATE gs_user_message SET user_key = '{user_key}' WHERE user_message_key = '{user_message_key}'")


    edifice_relation = findEdificeRelationByKey(user_key, edifice_key)
    if(edifice_relation is None):
        ## user_key / edifice_key / 
        edifice_relation = (user_key, edifice_key)
        insertEdificeRelation(edifice_relation)


    group_relation = findGroupRelationByKey(user_key, group_key);
    if(group_relation is None):
        ## user_key / group_key / edifice_key
        group_relation = (user_key, group_key, edifice_key)
        group = findGroupByKey(group_key);
        if(group is not None):
            insertGroupRelation(group_relation)
    


    ## 移动login_id
    if(user_message_key):
        user_message = findUserMessageByKey(user_message_key)
        if(login_id):
            updateUserMessageByKey(user_message_key, login_id=login_id)
        if(people is not None):
            updateUserMessageByKey(user_message_key, people=people)
        if(user_key):
            updateUserMessageByKey(user_message_key, user_key=user_key)




#     # ## 创建丢失的soft账号
#     # if(is_admin == 1):
#     #     soft = []
#     #     soft.append("user_" + random(27))
#     #     soft.append(datetime())
#     #     soft.append(datetime())
#     #     soft.append(1)
#     #     # is_admin = user[4]
#     #     soft.append(0)
#     #     # child_num = user[5]
#     #     soft.append(0)
#     #     # default_audit = user[6]
#     #     soft.append(0)
#     #     # department_key = user[7]
#     #     soft.append(None)
#     #     # edifice_key = user[8]
#     #     soft.append(edifice_key)
#     #     # group_key = user[9]
#     #     soft.append(None)
#     #     # language = user[10]
#     #     soft.append(None)
#     #     # login_id = user[11]
#     #     soft.append(None)
#     #     # origin_user_key = user[12]
#     #     soft.append(origin_user_key)
#     #     # parent_user_key = user[13]
#     #     soft.append(None)
#     #     # password = user[14]
#     #     soft.append(edifice_key)
#     #     # people = user[15]
#     #     soft.append(edifice_key)
#     #     # real_name = user[16]
#     #     soft.append('上位机')
#     #     # is_root = user[17]
#     #     soft.append('上位机')
#     #     root_user_key = user[18]
#     #     salt = user[19]
#     #     skin = user[20]
#     #     soft = user[21]
#     #     state = user[22]
#     #     user_message_key = user[23]
#     #     user_type = user[24]
#     #     username = user[25]
#     #     out_id = user[26]

# # 创建数据库表
# f = open("mysql/gonsin/modify_columns_after_data_translate.sql", "r", encoding="utf8")
# for line in f:
#     mycursor.execute(line)
# f.close()
# mydb.commit()



# 查询所有人员资料
def findAllUserMessage():
    mycursor.execute(f"SELECT user_message_key FROM gs_user_message")
    edifice_relation = mycursor.fetchall()
    return edifice_relation

## 查询人员资料和部门的关系
def findDepartmentRelationByUserMessageKey(user_message_key):
    mycursor.execute(f"SELECT relation_key FROM gs_user_message_department where user_message_key = '{user_message_key}'")
    edifice_relation = mycursor.fetchall()
    return edifice_relation


## 修改默认的人员资料和部门
def updateDefaultDisplay(relation_key, value):
    mycursor.execute(f"UPDATE gs_user_message_department SET default_display = {value}, create_time = 1694589231700 WHERE relation_key = '{relation_key}'")
    mydb.commit()


## 为所有人员资料设置默认显示的部门
userMessages = findAllUserMessage();
for userMessage in userMessages:
    user_message_key = userMessage[0]
    # 查询该人员属于多少个部门
    userMessageDepartmentRelations = findDepartmentRelationByUserMessageKey(user_message_key)
    if(userMessageDepartmentRelations == None):
        print(f"有人员不属于部门 {user_message_key}")
        continue;
    
    if(userMessageDepartmentRelations.__len__() >= 1):
        relation = userMessageDepartmentRelations[0]
        relation_key = relation[0]
        updateDefaultDisplay(relation_key, 1)
    

# if __name__ == 'main':
#     main()
